package service;

import Constant.Constant;
import collection.UtilCollection;
import common.Charset;
import common.NslHeadLineInfo;
import csv.UtilCsv;
import database.UtilDatabaseMetaInfo;
import database.UtilPreparedStatment;
import database.domain.ColumnInfo;
import excel.UtilExcelCellValue;
import excel.UtilExcelCommon;
import log4j.UtilLog4j;
import org.apache.log4j.Level;
import org.apache.log4j.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import string.UtilString;
import util.JdbcUtils;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/** 数据库中的列 和 csv中的列 数据，有直接对应关系
 * Description: insert
 * Created by E440 on 2017/5/18.
 */
public class SimpleCsv2Database {
    //导入数据的数据库表
     String dataBaseTableName="dx_lte_zte_cell_day";

     String configExcelFile;

     String configSheetName;

     NslHeadLineInfo csvHeadLineInfo;

     String csvDataFile;

     String csvCharset= Charset.GB2312;

     String insertSql;

     Connection connection=null;

     Logger logger= UtilLog4j.getLogger(SimpleCsv2Database.class);

    //保存 database columnName
    List<String> databaseColumnNameList=new ArrayList<>();

    //<databaseColumnName,csvColumnName>,记录 数据库中的列 和 导入数据csv文件中的列的对应关系
    Map<String,String> configRelationMap =new HashMap<>();

    //databaseColumnName, typeName
    Map<String,ColumnInfo> databaseColumnInfoMap =new HashMap<>();


   /* String insertSql="insert into dx_lte_zte_cell_day(`序号` ,`开始时间` ,`结束时间` ,`查询粒度` ,`子网` ,`子网名称` ,`网元` ,`网元名称` ,`小区` ,`小区名称` ,`eNodeB` ,`eNodeB名称` ,`[LTE]小区退服时长(秒)` ,`系统每PRB接收到的干扰噪声` ,`小区CQI0使用数量` ,`小区CQI1使用数量` ,`小区CQI2使用数量` ,`小区CQI3使用数量` ,`小区CQI4使用数量` ,`小区CQI5使用数量` ,`小区CQI6使用数量` ,`小区CQI7使用数量` ,`小区CQI8使用数量` ,`小区CQI9使用数量` ,`小区CQI10使用数量` ,`小区CQI11使用数量` ,`小区CQI12使用数量` ,`小区CQI13使用数量` ,`小区CQI14使用数量` ,`小区CQI15使用数量` ,`[LTE]平均CQI` ,`[LTE]RRC建立请求数目` ,`[LTE]RRC建立成功数目` ,`RRC连接建立平均用户数` ,`RRC连接建立最大用户数` ,`E-RAB建立请求次数` ,`E-RAB建立成功次数` ,`E-RAB建立拥塞个数` ,`LTE业务掉线率` ,`LTE业务掉线次数` ,`LTE业务释放次数` ,`同频切换请求次数` ,`同频切换成功次数` ,`异频切换请求次数` ,`异频切换成功次数` ,`L2U PS异系统重定向次数` ,`空口下行业务字节数(MByte)` ,`空口上行业务字节数(MByte)` ,`上行PRB平均占用率分子` ,`上行PRB平均占用率分母` ,`下行PRB平均占用率分子` ,`下行PRB平均占用率分母` )" +
            " values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";*/

    public SimpleCsv2Database(String dataBaseTableName, String configExcelFile, String configSheetName, String csvDataFile,Connection connection) throws InvalidFormatException, SQLException, IOException {
        this.dataBaseTableName = dataBaseTableName;
        this.configExcelFile = configExcelFile;
        this.configSheetName = configSheetName;
        this.csvDataFile = csvDataFile;
        this.connection=connection;
        logger.setLevel(Level.INFO);

        logger.debug("");
        logger.debug("*************************************************");
        logger.debug("insert csv data to database");
        logger.debug("databaseTableName : "+dataBaseTableName);
        logger.debug("configExcelFile : "+configExcelFile);
        logger.debug("configSheetName : "+configSheetName);
        logger.debug("csvDataFile : "+csvDataFile);
        logger.debug("----------------------------------------------------");


        init();
    }

    private void init() throws IOException, SQLException, InvalidFormatException {
        initDatabaseColumnNameListAndConfigRelationMap(configExcelFile,configSheetName);
        initDatabaseColumnInfoMap(connection);
        initCsvHeadLineInfo();
        initInsertSql();
    }


    /** 读取 插入数据的配置文件,以初始化 columnNameList;configRelationMap
     * @param fileAbsPath
     * @param sheetName
     * @throws IOException
     * @throws InvalidFormatException
     */
    //
    private void initDatabaseColumnNameListAndConfigRelationMap(String fileAbsPath,String sheetName) throws IOException, InvalidFormatException {
       Sheet sheet= UtilExcelCommon.getSheet(fileAbsPath,sheetName);
       int firstRowNumber=sheet.getFirstRowNum();
       int lastRowNumber=sheet.getLastRowNum();
       Row headRow=sheet.getRow(firstRowNumber);
       Map<String,Integer> headMap=new HashMap<>();
       String[] headNames={"数据库列名","原始数据列名"};
       for (String headName : headNames) {
            for (Cell cell : headRow) {
                String cellValue= UtilExcelCellValue.getCellValueAsString(cell);
                if(0 == cellValue.trim().compareToIgnoreCase(headName)){
                    headMap.put(headName,cell.getColumnIndex());
                }
            }
       }
       int databaseColumnNameIndex= headMap.get("数据库列名");
       int srcDataColumnNameIndex=headMap.get("原始数据列名");

       for(int i=firstRowNumber+1;i<=lastRowNumber;i++){
           Row row=sheet.getRow(i);
           String databaseColumnName=UtilExcelCellValue.getCellValueAsString(row.getCell(databaseColumnNameIndex));
           String srcDataColumnName=UtilExcelCellValue.getCellValueAsString(row.getCell(srcDataColumnNameIndex));
           databaseColumnNameList.add(databaseColumnName);
           configRelationMap.put(databaseColumnName,srcDataColumnName);
       }
       UtilCollection.removeNull(databaseColumnNameList);
        configRelationMap.remove(null);
        logger.debug("databaseColumnNameList : " +databaseColumnNameList);
        for (int i = 0; i < databaseColumnNameList.size(); i++) {
            logger.debug("i = "+i+" : "+databaseColumnNameList.get(i));
        }
        logger.debug("----------------------------------------------------");
        logger.debug("configRelationMap : " +configRelationMap);
        logger.debug("----------------------------------------------------");

    }

    private void initDatabaseColumnInfoMap(Connection con) throws SQLException {
        Map<String,ColumnInfo> all=UtilDatabaseMetaInfo.getColumnInfoMap(con, dataBaseTableName);
        for (String s : databaseColumnNameList) {
            databaseColumnInfoMap.put(s,all.get(s));
        }
    }

    private void initCsvHeadLineInfo() throws IOException {
        Set<String> headSet=new HashSet<>();
        headSet.addAll(configRelationMap.values());
        csvHeadLineInfo= UtilCsv.getHeadLineInfo(csvDataFile,headSet,csvCharset);
        logger.debug("headLineInfo : " +csvHeadLineInfo);
        logger.debug("----------------------------------------------------");
    }

    public void setParameters(PreparedStatement ps, List<String> dataLineList) throws SQLException {
        //System.out.println("columnNameList.size() = "+databaseColumnNameList.size());
        Map<String,Integer> csvColumnMap=csvHeadLineInfo.getHeadLineMap();
        Set<String> csvColumnNameSet=csvColumnMap.keySet();
        for (int i = 0; i < databaseColumnNameList.size(); i++) {
            //System.out.println("i = "+i);
            String databaseColumnName=databaseColumnNameList.get(i);
            String configCsvColumnName=configRelationMap.get(databaseColumnName);
            ColumnInfo columnInfo=databaseColumnInfoMap.get(databaseColumnName);
            //logger.debug("inserting.....  "+databaseColumnName);
            //特殊字段处理
            if(extraSetParameter(ps,i+1,databaseColumnName,configCsvColumnName,columnInfo,dataLineList)) {
                continue;
            }

            //直接对应的，直接插入数值,找不到的设置为null
            String dataValue=null;
            if(csvColumnNameSet.contains(configCsvColumnName)){
                int index=csvColumnMap.get(configCsvColumnName);
                dataValue=dataLineList.get(index);

                UtilPreparedStatment.setPsParameter(ps,i+1,columnInfo.getTypeInt(),dataValue);
            }else {
                System.out.println(columnInfo);
                ps.setNull(i+1,columnInfo.getTypeInt());
            }
            logger.debug("inserting   "+databaseColumnName+" , index = "+(i+1)+" , dataValue = "+dataValue);
        }
    }

    /** 对插入数据的特别处理，不同于一般的直接赋值
     * @param ps
     * @param dataLineList
     * @return true:进行了特殊处理，false:什么也没干
     */
    public boolean extraSetParameter(PreparedStatement ps,int psIndex,String databaseColumnName,String configCsvColumnName,  ColumnInfo columnInfo,List<String> dataLineList) throws SQLException {
        return false;
    }

    private void initInsertSql(){
        if(UtilString.isNullOrEmpty(dataBaseTableName) || databaseColumnNameList.size()==0 ){
            throw new RuntimeException("组装Insert Sql 失败： dataBaseTableName is null or databaseColumnNameList.size()=0 ");
        }
        String columnNames=UtilString.joinWithQuote(databaseColumnNameList,",","`");
        List<String> list=new ArrayList<>(columnNames.length());
        UtilCollection.fillElements(list,databaseColumnNameList.size(),"?");
        String parameters=UtilString.join(list,",");
        //System.out.println(list);
        insertSql="insert into "+dataBaseTableName+"("+columnNames+") values("+parameters+")";
        logger.debug("insertSql : " +insertSql);
        logger.debug("----------------------------------------------------");
    }


    public void inserDatabase() throws SQLException, IOException {
        //PreparedStatement ps = null;
        ResultSet rs = null;
        int count=0;
        try(PreparedStatement ps=connection.prepareStatement(insertSql)){
            try(Scanner sc = new Scanner(new BufferedInputStream(new FileInputStream(csvDataFile)), UtilString.GBK)) {
                int i=0,j=0;//i 记录正在插入的行号，j 记录批处理添加的条目数
                //跳过表头
                while (sc.hasNextLine()){
                    if(i>csvHeadLineInfo.getHeadLineIndex()){
                       break;
                    }
                    i++;
                    sc.nextLine();
                }
                while (sc.hasNextLine()) {
                    j++;
                    String dataLine = sc.nextLine();
                    // System.out.println(line);
                    List<String> dataLineList = Arrays.asList(dataLine.split(Constant.CSV_SPLIT_BY));
                    //System.out.println("dataLineList = " + dataLineList);
                    setParameters(ps, dataLineList);

                    ps.addBatch();
                    if(j%1000==0){
                        ps.executeBatch();
                        count+=j;
                        logger.debug("insert count :"+ count);
                        j=0;
                    }
                }
                ps.executeBatch();
                count=count+j;
                logger.debug("insert count :"+ count);
                logger.info("");
                logger.info("****************************************************");
                logger.info("insert csv data to database");
                logger.info("databaseTableName : "+dataBaseTableName);
                logger.info("configExcelFile : "+configExcelFile);
                logger.info("configSheetName : "+configSheetName);
                logger.info("csvDataFile : "+csvDataFile);
                logger.info("inserted record count : "+count);
                logger.info("****************************************************");

                // note that Scanner suppresses exceptions
                if (sc.ioException() != null) {
                    throw sc.ioException();
                }
            }
        }
    }

    public static void main(String[] args) throws Exception {
        String databaseName="dx_lte_zte_cell_day";
        String configExcel="D:\\Java_Test\\Excel_To_Database_v1.xlsx";
        String sheetName="Dx_Zte_227";
        String csvData="Z:\\BaiDu\\Syn\\Work\\2017年省公司工作\\电信共享\\5m\\DX_20170501-20170504_day_zte227.csv";
        Connection con= JdbcUtils.getConnection();
       SimpleCsv2Database simpleCsv2Database=new DxZte227CsvIntoDatabase(databaseName,configExcel,sheetName,csvData,con);
        //SimpleCsv2Database simpleCsv2Database=new SimpleCsv2Database(databaseName,configExcel,sheetName,csvData,con);
        simpleCsv2Database.inserDatabase();
    }
}
